{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1471c1c7-7a28-4f51-b08b-94ae8ea02211",
   "metadata": {},
   "source": [
    "# **Case Study: Generating Insights from E-Commerce Orders Data**\n",
    "\n",
    "### **What's covered in this notebook?**\n",
    "1. Reading JSON File\n",
    "2. Understanding the JSON Structure\n",
    "3. Creating Tables from JSON for Analysis\n",
    "\t- Normalizing JSON\n",
    "\t- Creating Delivery Table\n",
    "\t- Creating Payment Table\n",
    "\t- Creating Order Item Table\n",
    "\t- Creating Order Delivery History Table\n",
    "4. Brainstorming\n",
    "5. Top-Selling Categories & Products (By Quantity)\n",
    "6. Calculate Total Payment After Discounts\n",
    "\t- Why use a LEFT Join in this case?\n",
    "7. Most Popular Payment Methods\n",
    "8. Extract Latest Status for Each Order\n",
    "9. Compare All Order Statuses\n",
    "10. Delivery Performance Analysis\n",
    "\t- Handling Missing Values\n",
    "\t- Calculating Delivery Status Breakdown\n",
    "\t- Calculating On-Time and Delayed Delivery Rate\n",
    "\t- Calculating Average Delivery Time\n",
    "\t- Calculating Carrier Performance"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8592c828-f404-4de9-99d9-80f22324b879",
   "metadata": {},
   "source": [
    "## **Reading JSON File**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9846aa33-8c67-4f5f-801f-de63e373b5b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data loaded successfully inside a variable with <class 'list'> type and 1000000 orders.\n"
     ]
    }
   ],
   "source": [
    "import json\n",
    "\n",
    "with open(\"ecommerce_data/orders.json\", 'r') as file:\n",
    "    data = json.load(file)\n",
    "\n",
    "print(f\"Data loaded successfully inside a variable with {type(data)} type and {len(data)} orders.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "c8316e9d-3379-4d16-ad68-83a8a1255788",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"order_id\": \"ORD1\",\n",
      "        \"customer\": {\n",
      "            \"id\": 1639,\n",
      "            \"name\": \"Angela Griffin\",\n",
      "            \"email\": \"gibbsedward@example.org\",\n",
      "            \"address\": {\n",
      "                \"street\": \"24026 Darlene Ranch\",\n",
      "                \"city\": \"Angelashire\",\n",
      "                \"country\": \"Saint Helena\"\n",
      "            }\n",
      "        },\n",
      "        \"items\": [\n",
      "            {\n",
      "                \"product_id\": 25,\n",
      "                \"name\": \"Mountain Bike\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 599.99,\n",
      "                \"quantity\": 1\n",
      "            },\n",
      "            {\n",
      "                \"product_id\": 23,\n",
      "                \"name\": \"Tennis Racket\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 89.99,\n",
      "                \"quantity\": 2\n",
      "            }\n",
      "        ],\n",
      "        \"payment\": {\n",
      "            \"method\": \"Credit Card\",\n",
      "            \"transaction_id\": \"D005B042-A\",\n",
      "            \"discount_applied\": 2.48\n",
      "        },\n",
      "        \"delivery\": {\n",
      "            \"status\": \"Delivered\",\n",
      "            \"tracking_id\": \"51D506FA-5\",\n",
      "            \"shipping_company\": \"UPS\",\n",
      "            \"expected_delivery_date\": \"2025-03-14\"\n",
      "        },\n",
      "        \"order_history\": [\n",
      "            {\n",
      "                \"status\": \"Processing\",\n",
      "                \"timestamp\": \"2025-03-10T22:35:56\"\n",
      "            },\n",
      "            {\n",
      "                \"status\": \"Shipped\",\n",
      "                \"timestamp\": \"2025-03-11T22:35:56\"\n",
      "            },\n",
      "            {\n",
      "                \"status\": \"Delivered\",\n",
      "                \"timestamp\": \"2025-03-13T22:35:56\"\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"order_id\": \"ORD2\",\n",
      "        \"customer\": {\n",
      "            \"id\": 163,\n",
      "            \"name\": \"Sarah Moore\",\n",
      "            \"email\": \"zshelton@example.org\",\n",
      "            \"address\": {\n",
      "                \"street\": \"33466 Kristin Meadow Suite 060\",\n",
      "                \"city\": \"Lake Brittany\",\n",
      "                \"country\": \"Nigeria\"\n",
      "            }\n",
      "        },\n",
      "        \"items\": [\n",
      "            {\n",
      "                \"product_id\": 24,\n",
      "                \"name\": \"Hydration Backpack for Runners\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 59.99,\n",
      "                \"quantity\": 1\n",
      "            },\n",
      "            {\n",
      "                \"product_id\": 21,\n",
      "                \"name\": \"Yoga Mat with Non-Slip Surface\",\n",
      "                \"category\": \"Sports\",\n",
      "                \"price\": 39.99,\n",
      "                \"quantity\": 2\n",
      "            }\n",
      "        ],\n",
      "        \"payment\": {\n",
      "            \"method\": \"Credit Card\",\n",
      "            \"transaction_id\": \"C1A2BCA7-1\",\n",
      "            \"discount_applied\": 6.12\n",
      "        },\n",
      "        \"delivery\": {\n",
      "            \"status\": \"Delivered\",\n",
      "            \"tracking_id\": \"C188AB48-0\",\n",
      "            \"shipping_company\": \"FedEx\",\n",
      "            \"expected_delivery_date\": \"2025-03-12\"\n",
      "        },\n",
      "        \"order_history\": [\n",
      "            {\n",
      "                \"status\": \"Processing\",\n",
      "                \"timestamp\": \"2025-03-09T18:15:56\"\n",
      "            },\n",
      "            {\n",
      "                \"status\": \"Shipped\",\n",
      "                \"timestamp\": \"2025-03-11T18:15:56\"\n",
      "            },\n",
      "            {\n",
      "                \"status\": \"Delivered\",\n",
      "                \"timestamp\": \"2025-03-12T18:15:56\"\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"order_id\": \"ORD3\",\n",
      "        \"customer\": {\n",
      "            \"id\": 1814,\n",
      "            \"name\": \"Dwayne Hartman\",\n",
      "            \"email\": \"daviddyer@example.org\",\n",
      "            \"address\": {\n",
      "                \"street\": \"32036 Rodney Creek\",\n",
      "                \"city\": \"New Brandy\",\n",
      "                \"country\": \"Sierra Leone\"\n",
      "            }\n",
      "        },\n",
      "        \"items\": [\n",
      "            {\n",
      "                \"product_id\": 28,\n",
      "                \"name\": \"Noise-Canceling Office Headset\",\n",
      "                \"category\": \"Accessories\",\n",
      "                \"price\": 89.99,\n",
      "                \"quantity\": 1\n",
      "            }\n",
      "        ],\n",
      "        \"payment\": {\n",
      "            \"method\": \"Credit Card\",\n",
      "            \"transaction_id\": \"98F4114D-B\",\n",
      "            \"discount_applied\": 5.42\n",
      "        },\n",
      "        \"delivery\": {\n",
      "            \"status\": \"Processing\",\n",
      "            \"tracking_id\": null,\n",
      "            \"shipping_company\": null,\n",
      "            \"expected_delivery_date\": \"2025-03-18\"\n",
      "        },\n",
      "        \"order_history\": [\n",
      "            {\n",
      "                \"status\": \"Processing\",\n",
      "                \"timestamp\": \"2025-03-11T18:46:56\"\n",
      "            }\n",
      "        ]\n",
      "    }\n",
      "]\n"
     ]
    }
   ],
   "source": [
    "json_sample = json.dumps(data[0:3], indent=4)\n",
    "\n",
    "print(json_sample)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5727ff7c-5349-48f3-9ce0-dff35204ddbf",
   "metadata": {},
   "source": [
    "## **Understanding the JSON Structure**  \n",
    "Your JSON data contains the following key elements:\n",
    "\n",
    "1. `order_id` (Unique for each order)\n",
    "2. `customer` (Details about the customer)\n",
    "   - `customer_id`\n",
    "   - `name`\n",
    "   - `email`\n",
    "   - `address` (Street, City, Country)\n",
    "3. `items` (List of products purchased in the order)\n",
    "   - `product_id`\n",
    "   - `name`\n",
    "   - `category`\n",
    "   - `price`\n",
    "   - `quantity`\n",
    "4. `payment` (Payment details)\n",
    "   - `method`\n",
    "   - `transaction_id`\n",
    "   - `discount_applied`\n",
    "5. `delivery` (Delivery details)\n",
    "   - `status`\n",
    "   - `tracking_id`\n",
    "   - `shipping_company`\n",
    "   - `expected_delivery_date`\n",
    "6. `order_history` (List of order statuses with timestamps)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e4ed30e-188e-41de-9b30-fa469dde5a96",
   "metadata": {},
   "source": [
    "## **Creating Tables from JSON for Analysis**\n",
    "\n",
    "---\n",
    "\n",
    "To normalize the data, we can create the following **five tables**:\n",
    "\n",
    "#### **Orders Table**\n",
    "| order_id | customer_id |\n",
    "|----------|------------|\n",
    "| ORD12345 | 98765      |\n",
    "| ORD67890 | 54321      |\n",
    "| ORD24680 | 11223      |\n",
    "\n",
    "> **Reason:** Orders should have a **one-to-one** relationship with customers. The `customer_id` acts as a **foreign key**.\n",
    "\n",
    "---\n",
    "\n",
    "#### **Customers Table**\n",
    "| customer_id | name        | email                  | street       | city         | country |\n",
    "|------------|------------|----------------------|-------------|-------------|---------|\n",
    "| 98765      | John Doe   | johndoe@email.com    | 123 Main St | New York    | USA     |\n",
    "| 54321      | Jane Smith | janesmith@email.com  | 456 Elm St  | Los Angeles | USA     |\n",
    "| 11223      | Alice Johnson | alicejohnson@email.com | 789 Oak St  | Chicago  | USA     |\n",
    "\n",
    "> **Reason:** Customer data should be stored separately for **data reusability**.\n",
    "\n",
    "---\n",
    "\n",
    "#### **Order Items Table**\n",
    "| order_id | product_id | name                     | category      | price  | quantity |\n",
    "|----------|------------|-------------------------|--------------|--------|----------|\n",
    "| ORD12345 | 111        | Laptop                  | Electronics   | 1500.00 | 1        |\n",
    "| ORD12345 | 222        | Wireless Mouse          | Accessories   | 50.00  | 2        |\n",
    "| ORD67890 | 333        | Smartphone              | Electronics   | 999.99 | 1        |\n",
    "| ORD24680 | 444        | Gaming Headset          | Accessories   | 199.99 | 1        |\n",
    "| ORD24680 | 555        | Mechanical Keyboard     | Accessories   | 120.00 | 1        |\n",
    "\n",
    "> **Reason:** Orders can have **multiple items**, so a **separate table** is needed.\n",
    "\n",
    "---\n",
    "\n",
    "#### **Payments Table**\n",
    "| order_id | payment_method | payment_transaction_id | payment_discount_applied |\n",
    "|----------|---------------|------------------------|-------------------------|\n",
    "| ORD12345 | Credit Card   | TXN78910               | 10.00                   |\n",
    "| ORD67890 | PayPal        | TXN65432               | 5.00                    |\n",
    "| ORD24680 | Debit Card    | TXN98765               | 15.00                   |\n",
    "\n",
    "> **Reason:** Payment details should be **stored separately** to maintain **transaction integrity**.\n",
    "\n",
    "---\n",
    "\n",
    "#### **Order History Table**\n",
    "| order_id | status     | timestamp             |\n",
    "|----------|-----------|-----------------------|\n",
    "| ORD12345 | Processing | 2025-03-16T10:00:00  |\n",
    "| ORD12345 | Shipped   | 2025-03-17T12:00:00  |\n",
    "| ORD12345 | Delivered | 2025-03-19T15:00:00  |\n",
    "| ORD67890 | Processing | 2025-03-16T14:00:00  |\n",
    "| ORD67890 | Shipped   | 2025-03-18T10:00:00  |\n",
    "| ORD24680 | Processing | 2025-03-17T09:30:00  |\n",
    "\n",
    "> **Reason:** Orders go through multiple statuses, so we need a **history table**.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0fcad7ca-dedc-4853-94ba-6794e2a65d41",
   "metadata": {},
   "source": [
    "### **Normalizing JSON**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8e52315b-d1b0-42ca-86cb-a80fecf86fc1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>items</th>\n",
       "      <th>order_history</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>customer_name</th>\n",
       "      <th>customer_email</th>\n",
       "      <th>customer_address_street</th>\n",
       "      <th>customer_address_city</th>\n",
       "      <th>customer_address_country</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_transaction_id</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>[{'product_id': 25, 'name': 'Mountain Bike', '...</td>\n",
       "      <td>[{'status': 'Processing', 'timestamp': '2025-0...</td>\n",
       "      <td>1639</td>\n",
       "      <td>Angela Griffin</td>\n",
       "      <td>gibbsedward@example.org</td>\n",
       "      <td>24026 Darlene Ranch</td>\n",
       "      <td>Angelashire</td>\n",
       "      <td>Saint Helena</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>D005B042-A</td>\n",
       "      <td>2.48</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>51D506FA-5</td>\n",
       "      <td>UPS</td>\n",
       "      <td>2025-03-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>[{'product_id': 24, 'name': 'Hydration Backpac...</td>\n",
       "      <td>[{'status': 'Processing', 'timestamp': '2025-0...</td>\n",
       "      <td>163</td>\n",
       "      <td>Sarah Moore</td>\n",
       "      <td>zshelton@example.org</td>\n",
       "      <td>33466 Kristin Meadow Suite 060</td>\n",
       "      <td>Lake Brittany</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>C1A2BCA7-1</td>\n",
       "      <td>6.12</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>C188AB48-0</td>\n",
       "      <td>FedEx</td>\n",
       "      <td>2025-03-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>[{'product_id': 28, 'name': 'Noise-Canceling O...</td>\n",
       "      <td>[{'status': 'Processing', 'timestamp': '2025-0...</td>\n",
       "      <td>1814</td>\n",
       "      <td>Dwayne Hartman</td>\n",
       "      <td>daviddyer@example.org</td>\n",
       "      <td>32036 Rodney Creek</td>\n",
       "      <td>New Brandy</td>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>98F4114D-B</td>\n",
       "      <td>5.42</td>\n",
       "      <td>Processing</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2025-03-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD4</td>\n",
       "      <td>[{'product_id': 10, 'name': 'Noise-Isolating E...</td>\n",
       "      <td>[{'status': 'Processing', 'timestamp': '2025-0...</td>\n",
       "      <td>4085</td>\n",
       "      <td>Michael Walters</td>\n",
       "      <td>michele19@example.com</td>\n",
       "      <td>09372 Collins Meadows</td>\n",
       "      <td>New Kimberly</td>\n",
       "      <td>Congo</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>269AE633-8</td>\n",
       "      <td>14.43</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>B3D21590-A</td>\n",
       "      <td>USPS</td>\n",
       "      <td>2025-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD5</td>\n",
       "      <td>[{'product_id': 2, 'name': '4K Ultra HD Smart ...</td>\n",
       "      <td>[{'status': 'Processing', 'timestamp': '2025-0...</td>\n",
       "      <td>2908</td>\n",
       "      <td>Heather Jones</td>\n",
       "      <td>jshaw@example.net</td>\n",
       "      <td>479 Kimberly Ville Suite 888</td>\n",
       "      <td>Port Taylor</td>\n",
       "      <td>Grenada</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>2C6598E5-B</td>\n",
       "      <td>464.80</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>649F931C-3</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id                                              items  \\\n",
       "0     ORD1  [{'product_id': 25, 'name': 'Mountain Bike', '...   \n",
       "1     ORD2  [{'product_id': 24, 'name': 'Hydration Backpac...   \n",
       "2     ORD3  [{'product_id': 28, 'name': 'Noise-Canceling O...   \n",
       "3     ORD4  [{'product_id': 10, 'name': 'Noise-Isolating E...   \n",
       "4     ORD5  [{'product_id': 2, 'name': '4K Ultra HD Smart ...   \n",
       "\n",
       "                                       order_history  customer_id  \\\n",
       "0  [{'status': 'Processing', 'timestamp': '2025-0...         1639   \n",
       "1  [{'status': 'Processing', 'timestamp': '2025-0...          163   \n",
       "2  [{'status': 'Processing', 'timestamp': '2025-0...         1814   \n",
       "3  [{'status': 'Processing', 'timestamp': '2025-0...         4085   \n",
       "4  [{'status': 'Processing', 'timestamp': '2025-0...         2908   \n",
       "\n",
       "     customer_name           customer_email         customer_address_street  \\\n",
       "0   Angela Griffin  gibbsedward@example.org             24026 Darlene Ranch   \n",
       "1      Sarah Moore     zshelton@example.org  33466 Kristin Meadow Suite 060   \n",
       "2   Dwayne Hartman    daviddyer@example.org              32036 Rodney Creek   \n",
       "3  Michael Walters    michele19@example.com           09372 Collins Meadows   \n",
       "4    Heather Jones        jshaw@example.net    479 Kimberly Ville Suite 888   \n",
       "\n",
       "  customer_address_city customer_address_country payment_method  \\\n",
       "0           Angelashire             Saint Helena    Credit Card   \n",
       "1         Lake Brittany                  Nigeria    Credit Card   \n",
       "2            New Brandy             Sierra Leone    Credit Card   \n",
       "3          New Kimberly                    Congo    Credit Card   \n",
       "4           Port Taylor                  Grenada    Credit Card   \n",
       "\n",
       "  payment_transaction_id  payment_discount_applied delivery_status  \\\n",
       "0             D005B042-A                      2.48       Delivered   \n",
       "1             C1A2BCA7-1                      6.12       Delivered   \n",
       "2             98F4114D-B                      5.42      Processing   \n",
       "3             269AE633-8                     14.43         Shipped   \n",
       "4             2C6598E5-B                    464.80       Delivered   \n",
       "\n",
       "  delivery_tracking_id delivery_shipping_company  \\\n",
       "0           51D506FA-5                       UPS   \n",
       "1           C188AB48-0                     FedEx   \n",
       "2                 None                      None   \n",
       "3           B3D21590-A                      USPS   \n",
       "4           649F931C-3                  BlueDart   \n",
       "\n",
       "  delivery_expected_delivery_date  \n",
       "0                      2025-03-14  \n",
       "1                      2025-03-12  \n",
       "2                      2025-03-18  \n",
       "3                      2025-03-13  \n",
       "4                      2025-03-16  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.json_normalize(data, sep=\"_\")\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "82022a15-dbe4-4543-9b4f-8fbea5faa55a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['order_id', 'items', 'order_history', 'customer_id', 'customer_name',\n",
       "       'customer_email', 'customer_address_street', 'customer_address_city',\n",
       "       'customer_address_country', 'payment_method', 'payment_transaction_id',\n",
       "       'payment_discount_applied', 'delivery_status', 'delivery_tracking_id',\n",
       "       'delivery_shipping_company', 'delivery_expected_delivery_date'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "539b761b-ba90-4dd2-9f24-997e4c4e6638",
   "metadata": {},
   "source": [
    "### **Creating Delivery Table**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "34ea2554-0b41-4809-b5db-72843218b359",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>51D506FA-5</td>\n",
       "      <td>UPS</td>\n",
       "      <td>2025-03-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>C188AB48-0</td>\n",
       "      <td>FedEx</td>\n",
       "      <td>2025-03-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>Processing</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2025-03-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD4</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>B3D21590-A</td>\n",
       "      <td>USPS</td>\n",
       "      <td>2025-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD5</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>649F931C-3</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id delivery_status delivery_tracking_id delivery_shipping_company  \\\n",
       "0     ORD1       Delivered           51D506FA-5                       UPS   \n",
       "1     ORD2       Delivered           C188AB48-0                     FedEx   \n",
       "2     ORD3      Processing                 None                      None   \n",
       "3     ORD4         Shipped           B3D21590-A                      USPS   \n",
       "4     ORD5       Delivered           649F931C-3                  BlueDart   \n",
       "\n",
       "  delivery_expected_delivery_date  \n",
       "0                      2025-03-14  \n",
       "1                      2025-03-12  \n",
       "2                      2025-03-18  \n",
       "3                      2025-03-13  \n",
       "4                      2025-03-16  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extracting only the delivery-related fields\n",
    "df_deliveries = df[[\"order_id\", \"delivery_status\", \"delivery_tracking_id\", \n",
    "                  \"delivery_shipping_company\", \"delivery_expected_delivery_date\"]].copy()\n",
    "\n",
    "df_deliveries.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a52d7e3-0ebb-4083-a26d-57e984cab959",
   "metadata": {},
   "source": [
    "### **Creating Payment Table**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "354a279d-469c-41ef-a325-80833c4128a9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_transaction_id</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>D005B042-A</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>C1A2BCA7-1</td>\n",
       "      <td>6.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>98F4114D-B</td>\n",
       "      <td>5.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD4</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>269AE633-8</td>\n",
       "      <td>14.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD5</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>2C6598E5-B</td>\n",
       "      <td>464.80</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id payment_method payment_transaction_id  payment_discount_applied\n",
       "0     ORD1    Credit Card             D005B042-A                      2.48\n",
       "1     ORD2    Credit Card             C1A2BCA7-1                      6.12\n",
       "2     ORD3    Credit Card             98F4114D-B                      5.42\n",
       "3     ORD4    Credit Card             269AE633-8                     14.43\n",
       "4     ORD5    Credit Card             2C6598E5-B                    464.80"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extracting only the payment-related fields\n",
    "df_payments = df[[\"order_id\", \"payment_method\", \"payment_transaction_id\", \n",
    "                  \"payment_discount_applied\"]].copy()\n",
    "\n",
    "df_payments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee80caf4-4faa-4767-9360-45d73c814830",
   "metadata": {},
   "source": [
    "### **Creating Order Item Table**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "4ea2185e-8219-4f25-9708-d7e083453f65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>order_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25</td>\n",
       "      <td>Mountain Bike</td>\n",
       "      <td>Sports</td>\n",
       "      <td>599.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23</td>\n",
       "      <td>Tennis Racket</td>\n",
       "      <td>Sports</td>\n",
       "      <td>89.99</td>\n",
       "      <td>2</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>Hydration Backpack for Runners</td>\n",
       "      <td>Sports</td>\n",
       "      <td>59.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>Yoga Mat with Non-Slip Surface</td>\n",
       "      <td>Sports</td>\n",
       "      <td>39.99</td>\n",
       "      <td>2</td>\n",
       "      <td>ORD2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Noise-Canceling Office Headset</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>89.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                            name     category   price  quantity  \\\n",
       "0          25                   Mountain Bike       Sports  599.99         1   \n",
       "1          23                   Tennis Racket       Sports   89.99         2   \n",
       "2          24  Hydration Backpack for Runners       Sports   59.99         1   \n",
       "3          21  Yoga Mat with Non-Slip Surface       Sports   39.99         2   \n",
       "4          28  Noise-Canceling Office Headset  Accessories   89.99         1   \n",
       "\n",
       "  order_id  \n",
       "0     ORD1  \n",
       "1     ORD1  \n",
       "2     ORD2  \n",
       "3     ORD2  \n",
       "4     ORD3  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_order_items = pd.json_normalize(data, \n",
    "                                  sep=\"_\", \n",
    "                                  record_path=[\"items\"], \n",
    "                                  meta=[\"order_id\"]\n",
    "                                  )\n",
    "\n",
    "df_order_items.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daf3210c-2af0-4747-8352-58d85cd46df5",
   "metadata": {},
   "source": [
    "### **Creating Order Delivery History Table**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ccfd1d73-c522-4536-8a49-ab3e92a74024",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>status</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>order_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-10T22:35:56</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-11T22:35:56</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-13T22:35:56</td>\n",
       "      <td>ORD1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-09T18:15:56</td>\n",
       "      <td>ORD2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-11T18:15:56</td>\n",
       "      <td>ORD2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       status            timestamp order_id\n",
       "0  Processing  2025-03-10T22:35:56     ORD1\n",
       "1     Shipped  2025-03-11T22:35:56     ORD1\n",
       "2   Delivered  2025-03-13T22:35:56     ORD1\n",
       "3  Processing  2025-03-09T18:15:56     ORD2\n",
       "4     Shipped  2025-03-11T18:15:56     ORD2"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract Order History in a Relational Format\n",
    "df_order_history = pd.json_normalize(data, \n",
    "                                     sep=\"_\",\n",
    "                                     record_path=[\"order_history\"], \n",
    "                                     meta=[\"order_id\"])\n",
    "\n",
    "df_order_history.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed2bd1fe-ba06-4657-9fbc-ed73a4c85374",
   "metadata": {},
   "source": [
    "## **Brainstorming**\n",
    "When dealing with millions of orders, businesses must analyze vast amounts of data efficiently. Without structured insights, decision-making becomes reactive instead of proactive. Here’s why this level of analysis is critical for scaling operations, improving profitability, and enhancing customer experience. \n",
    "\n",
    "**Business Goal:** \n",
    "- Identify best-performing products & categories to optimize inventory and marketing.\n",
    "- Understand customer payment preferences to optimize checkout experience.\n",
    "- Ensure correct discount application and understand pricing trends.\n",
    "- Provide real-time order tracking insights.\n",
    "- Analyze order processing efficiency.\n",
    "- Identify bottlenecks in shipping & improve logistics efficiency.\n",
    "\n",
    "**Questions to Analyse:**\n",
    "- What are the top 10 best-selling products by total quantity sold?\n",
    "- What are the top 10 best-selling products by total revenue?\n",
    "- What are the most used payment methods across all orders?\n",
    "- Do certain payment methods have higher total revenue than others?\n",
    "- Are certain payment methods associated with higher refund or failed transactions?\n",
    "- What is the total amount paid for each order after discount?\n",
    "- How much revenue was lost due to discounts?\n",
    "- What is the latest recorded status for each order?\n",
    "- How many orders are currently in each stage of the process (Processing, Shipped, Delivered, Cancelled)?\n",
    "- Which shipping company has the best on-time delivery rate?\n",
    "- etc..."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd9ac90a-3133-4479-a1db-d1fe6528b80f",
   "metadata": {},
   "source": [
    "## **Top-Selling Categories & Products (By Quantity)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4ea334a0-8d6e-4893-a6d9-f2d762847c19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name\n",
      "Hydration Backpack for Runners         485118\n",
      "Tennis Racket                          403387\n",
      "Wireless Noise-Canceling Headphones    403080\n",
      "Bluetooth Portable Speaker             243451\n",
      "4K Ultra HD Smart TV                   242775\n",
      "Mountain Bike                          242755\n",
      "Noise-Isolating Earbuds                242642\n",
      "Winter Puffer Jacket                   241756\n",
      "Yoga Mat with Non-Slip Surface         161800\n",
      "Ergonomic Wireless Mouse               161512\n",
      "Home Theater Speaker System            161442\n",
      "Adjustable Standing Desk               160239\n",
      "High-Powered Vacuum Cleaner             81466\n",
      "Slim Fit Denim Jeans                    81364\n",
      "Ceramic Dinnerware Set                  81349\n",
      "Premium Memory Foam Pillow              81315\n",
      "Men's Waterproof Jacket                 81176\n",
      "Smartwatch with Heart Rate Monitor      81058\n",
      "Stainless Steel Cookware Set            81000\n",
      "USB-C Charging Dock                     80891\n",
      "Cotton Crew Neck T-Shirt                80852\n",
      "Adjustable Dumbbell Set                 80834\n",
      "Running Shoes                           80660\n",
      "Smart Home Security Camera              80567\n",
      "Smartphone 5G                           80553\n",
      "Mechanical Gaming Keyboard              80480\n",
      "Woolen Winter Gloves                    80211\n",
      "Noise-Canceling Office Headset          79987\n",
      "Professional DSLR Camera                40564\n",
      "Gaming Laptop                           40541\n",
      "Name: quantity, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "top_products = df_order_items.groupby(\"name\")[\"quantity\"].sum().sort_values(ascending=False)\n",
    "\n",
    "print(top_products)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "97abe49f-4b55-4d31-baf9-8f5a3b0452c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Top Categories:\n",
      " category\n",
      "Sports         1373894\n",
      "Electronics    1292973\n",
      "Accessories     726570\n",
      "Clothing        646019\n",
      "Home            485369\n",
      "Name: quantity, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "top_categories = df_order_items.groupby(\"category\")[\"quantity\"].sum().sort_values(ascending=False)\n",
    "\n",
    "print(\"Top Categories:\\n\", top_categories)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "238acf19-3fc4-4448-ace8-a269aa5b2d54",
   "metadata": {},
   "source": [
    "## **Most Popular Payment Methods**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ad0ddf3a-d710-4252-8211-12c9b82d091f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "payment_method\n",
      "Credit Card      499622\n",
      "PayPal           300750\n",
      "Debit Card       149589\n",
      "Bank Transfer     50039\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "top_payment_methods = df_payments[\"payment_method\"].value_counts()\n",
    "\n",
    "print(top_payment_methods)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93edcaeb-2889-4ef0-bd81-8214532420cd",
   "metadata": {},
   "source": [
    "## **Calculate Total Payment After Discounts**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "ee20b56d-feef-4a48-935b-ead41e86b8b7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>product_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "      <th>price</th>\n",
       "      <th>quantity</th>\n",
       "      <th>order_id</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25</td>\n",
       "      <td>Mountain Bike</td>\n",
       "      <td>Sports</td>\n",
       "      <td>599.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>599.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>23</td>\n",
       "      <td>Tennis Racket</td>\n",
       "      <td>Sports</td>\n",
       "      <td>89.99</td>\n",
       "      <td>2</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>179.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>Hydration Backpack for Runners</td>\n",
       "      <td>Sports</td>\n",
       "      <td>59.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD2</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21</td>\n",
       "      <td>Yoga Mat with Non-Slip Surface</td>\n",
       "      <td>Sports</td>\n",
       "      <td>39.99</td>\n",
       "      <td>2</td>\n",
       "      <td>ORD2</td>\n",
       "      <td>79.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>Noise-Canceling Office Headset</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>89.99</td>\n",
       "      <td>1</td>\n",
       "      <td>ORD3</td>\n",
       "      <td>89.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                            name     category   price  quantity  \\\n",
       "0          25                   Mountain Bike       Sports  599.99         1   \n",
       "1          23                   Tennis Racket       Sports   89.99         2   \n",
       "2          24  Hydration Backpack for Runners       Sports   59.99         1   \n",
       "3          21  Yoga Mat with Non-Slip Surface       Sports   39.99         2   \n",
       "4          28  Noise-Canceling Office Headset  Accessories   89.99         1   \n",
       "\n",
       "  order_id  total_amount  \n",
       "0     ORD1        599.99  \n",
       "1     ORD1        179.98  \n",
       "2     ORD2         59.99  \n",
       "3     ORD2         79.98  \n",
       "4     ORD3         89.99  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_order_items[\"total_amount\"] = df_order_items[\"price\"] * df_order_items[\"quantity\"]\n",
    "\n",
    "df_order_items.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a1540578-26a1-4c6b-9d07-6a30e56f08e2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>779.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD10</td>\n",
       "      <td>59.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>159.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000</td>\n",
       "      <td>629.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>259.97</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  total_amount\n",
       "0      ORD1        779.97\n",
       "1     ORD10         59.99\n",
       "2    ORD100        159.98\n",
       "3   ORD1000        629.96\n",
       "4  ORD10000        259.97"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Aggregate total amount per order\n",
    "df_total = df_order_items.groupby(\"order_id\", as_index=False)[\"total_amount\"].sum()\n",
    "\n",
    "df_total.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cd0322f0-793d-45ce-bd62-54eaaf3e9032",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_transaction_id</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>779.97</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>D005B042-A</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD10</td>\n",
       "      <td>59.99</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>25A6367A-9</td>\n",
       "      <td>1.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>159.98</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>B54E4367-7</td>\n",
       "      <td>1.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000</td>\n",
       "      <td>629.96</td>\n",
       "      <td>Debit Card</td>\n",
       "      <td>673C4FBF-D</td>\n",
       "      <td>63.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>259.97</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>5A384D39-3</td>\n",
       "      <td>8.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  total_amount payment_method payment_transaction_id  \\\n",
       "0      ORD1        779.97    Credit Card             D005B042-A   \n",
       "1     ORD10         59.99    Credit Card             25A6367A-9   \n",
       "2    ORD100        159.98    Credit Card             B54E4367-7   \n",
       "3   ORD1000        629.96     Debit Card             673C4FBF-D   \n",
       "4  ORD10000        259.97    Credit Card             5A384D39-3   \n",
       "\n",
       "   payment_discount_applied  \n",
       "0                      2.48  \n",
       "1                      1.36  \n",
       "2                      1.01  \n",
       "3                     63.49  \n",
       "4                      8.60  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# # Merge with payment details\n",
    "df_merged = df_total.merge(df_payments, on=\"order_id\", how=\"left\")\n",
    "\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9435b098-e76f-43f1-af7e-aaa307690661",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>payment_method</th>\n",
       "      <th>payment_transaction_id</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "      <th>total_payment_after_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>779.97</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>D005B042-A</td>\n",
       "      <td>2.48</td>\n",
       "      <td>777.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD10</td>\n",
       "      <td>59.99</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>25A6367A-9</td>\n",
       "      <td>1.36</td>\n",
       "      <td>58.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>159.98</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>B54E4367-7</td>\n",
       "      <td>1.01</td>\n",
       "      <td>158.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000</td>\n",
       "      <td>629.96</td>\n",
       "      <td>Debit Card</td>\n",
       "      <td>673C4FBF-D</td>\n",
       "      <td>63.49</td>\n",
       "      <td>566.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>259.97</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>5A384D39-3</td>\n",
       "      <td>8.60</td>\n",
       "      <td>251.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  total_amount payment_method payment_transaction_id  \\\n",
       "0      ORD1        779.97    Credit Card             D005B042-A   \n",
       "1     ORD10         59.99    Credit Card             25A6367A-9   \n",
       "2    ORD100        159.98    Credit Card             B54E4367-7   \n",
       "3   ORD1000        629.96     Debit Card             673C4FBF-D   \n",
       "4  ORD10000        259.97    Credit Card             5A384D39-3   \n",
       "\n",
       "   payment_discount_applied  total_payment_after_discount  \n",
       "0                      2.48                        777.49  \n",
       "1                      1.36                         58.63  \n",
       "2                      1.01                        158.97  \n",
       "3                     63.49                        566.47  \n",
       "4                      8.60                        251.37  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Compute the final payment after discount\n",
    "df_merged[\"total_payment_after_discount\"] = df_merged[\"total_amount\"] - df_merged[\"payment_discount_applied\"]\n",
    "\n",
    "# Display the result\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0bfab5a9-14b2-4bbd-ad27-d72e87ed8a30",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_amount</th>\n",
       "      <th>payment_discount_applied</th>\n",
       "      <th>total_payment_after_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1000000.00000</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1064.811247</td>\n",
       "      <td>79.92195</td>\n",
       "      <td>984.889297</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>942.870049</td>\n",
       "      <td>93.93399</td>\n",
       "      <td>874.165848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>19.990000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>16.990000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>359.950000</td>\n",
       "      <td>16.83000</td>\n",
       "      <td>328.690000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>749.970000</td>\n",
       "      <td>45.11000</td>\n",
       "      <td>688.070000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1559.970000</td>\n",
       "      <td>107.24000</td>\n",
       "      <td>1444.690000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>8999.890000</td>\n",
       "      <td>1133.46000</td>\n",
       "      <td>8134.020000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         total_amount  payment_discount_applied  total_payment_after_discount\n",
       "count  1000000.000000             1000000.00000                1000000.000000\n",
       "mean      1064.811247                  79.92195                    984.889297\n",
       "std        942.870049                  93.93399                    874.165848\n",
       "min         19.990000                   0.00000                     16.990000\n",
       "25%        359.950000                  16.83000                    328.690000\n",
       "50%        749.970000                  45.11000                    688.070000\n",
       "75%       1559.970000                 107.24000                   1444.690000\n",
       "max       8999.890000                1133.46000                   8134.020000"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged.describe()\n",
    "\n",
    "# NOTE: All three columns values are independent of each other"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6e8684c2-f8a0-428c-a947-d8a0d1dcd1f1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Revenue After Discount: 984889296.7699994\n"
     ]
    }
   ],
   "source": [
    "print(f\"Total Revenue After Discount: {df_merged['total_payment_after_discount'].sum()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c9100c0-7fb7-4da4-87a1-385d2817b578",
   "metadata": {},
   "source": [
    "### **Why use a LEFT Join in this case?**\n",
    "\n",
    "We have two tables:\n",
    "1. df_total (Left Table): Contains order_id and the total amount of all items in that order.\n",
    "2. df_payments (Right Table): Contains order_id, payment details, and the discount applied.\n",
    "\n",
    "**Our goal:** Compute the total payment after discount for each order.\n",
    "\n",
    "**Why NOT Use an INNER JOIN?**\n",
    "```python\n",
    "df_merged = df_total.merge(df_payments, on=\"order_id\", how=\"inner\")\n",
    "```\n",
    "If we use the above it would only keep orders that exist in both tables. This means:\n",
    "\n",
    "1. Orders present in both df_total and df_payments will be included.\n",
    "2. If an order exists in df_total but has no payment record in df_payments, it will be excluded.\n",
    "\n",
    "This is not desirable because we want to keep all order totals, even if no payment record exists.\n",
    "\n",
    "**Why LEFT JOIN is the Best Choice?**  \n",
    "1. We keep all orders from df_total (because they represent actual purchases).\n",
    "2. If a payment record exists in df_payments, it gets merged in.\n",
    "3. If a payment record does NOT exist, we still keep the order, but NaN appears in the payment_discount_applied column.\n",
    "\n",
    "**What Happens to Missing Orders?**\n",
    "\n",
    "Let’s assume ORD4 exists in df_payments but not in df_orders. A LEFT JOIN ensures that:\n",
    "\n",
    "| order_id | total_amount | payment_discount_applied |\n",
    "|----------|-------------|-------------------------|\n",
    "| ORD1     | 64.98       | 1.93                    |\n",
    "| ORD2     | 389.97      | 23.34                   |\n",
    "| ORD3     | 489.95      | 184.62                  |\n",
    "| ORD4     | NaN         | 20.87                   |  \n",
    "| ORD5     | NaN         | 30.88                   |\n",
    "\n",
    "Here, ORD4 and ORD5 appear with NaN in total_amount, meaning no order records exist for them, which we can handle later (e.g., replace NaN with 0)."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56060b67-98d1-4fd6-91a7-632d72ebdeb5",
   "metadata": {},
   "source": [
    "## **Extract Latest Status for Each Order**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e3a89882-f09b-4ac5-9173-f83de6d2fd6e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>status</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-13 22:35:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD10</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-12 20:30:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-16 18:54:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-17 20:20:56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-09 18:23:58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id      status           timestamp\n",
       "0      ORD1   Delivered 2025-03-13 22:35:56\n",
       "1     ORD10  Processing 2025-03-12 20:30:56\n",
       "2    ORD100  Processing 2025-03-16 18:54:56\n",
       "3   ORD1000   Delivered 2025-03-17 20:20:56\n",
       "4  ORD10000  Processing 2025-03-09 18:23:58"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert timestamp to datetime for sorting\n",
    "df_order_history[\"timestamp\"] = pd.to_datetime(df_order_history[\"timestamp\"])\n",
    "\n",
    "# Get latest status for each order\n",
    "df_latest_status = df_order_history.sort_values(by=[\"order_id\", \"timestamp\"]).groupby(\"order_id\").last().reset_index()\n",
    "\n",
    "df_latest_status.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d95320ba-09c9-4ef7-a593-b354cd594a9c",
   "metadata": {},
   "source": [
    "## **Compare All Order Statuses**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "700933f8-7be4-4310-ab4f-6bc20094a648",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>status</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>order_id</th>\n",
       "      <th>step</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-10 22:35:56</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-11 22:35:56</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-13 22:35:56</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-09 18:15:56</td>\n",
       "      <td>ORD2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-11 18:15:56</td>\n",
       "      <td>ORD2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       status           timestamp order_id  step\n",
       "0  Processing 2025-03-10 22:35:56     ORD1     1\n",
       "1     Shipped 2025-03-11 22:35:56     ORD1     2\n",
       "2   Delivered 2025-03-13 22:35:56     ORD1     3\n",
       "3  Processing 2025-03-09 18:15:56     ORD2     1\n",
       "4     Shipped 2025-03-11 18:15:56     ORD2     2"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assign a unique step number for each order status\n",
    "df_order_history[\"step\"] = df_order_history.groupby(\"order_id\").cumcount() + 1\n",
    "\n",
    "df_order_history.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "ec8e6066-7211-4fad-ac97-df990cd990d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>step</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>order_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ORD1</th>\n",
       "      <td>Processing</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORD10</th>\n",
       "      <td>Processing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORD100</th>\n",
       "      <td>Processing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORD1000</th>\n",
       "      <td>Processing</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Delivered</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ORD10000</th>\n",
       "      <td>Processing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "step               1        2          3\n",
       "order_id                                \n",
       "ORD1      Processing  Shipped  Delivered\n",
       "ORD10     Processing      NaN        NaN\n",
       "ORD100    Processing      NaN        NaN\n",
       "ORD1000   Processing  Shipped  Delivered\n",
       "ORD10000  Processing      NaN        NaN"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Pivot the data to spread out the statuses\n",
    "df_pivot = df_order_history.pivot(index=\"order_id\", columns=\"step\", values=\"status\")\n",
    "\n",
    "df_pivot.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba2adf3d-54ac-4f51-a861-c5a3341e420c",
   "metadata": {},
   "source": [
    "## **Delivery Performance Analysis**\n",
    "\n",
    "Delivery Performance Analysis evaluates the efficiency and reliability of order fulfillment by measuring key delivery metrics. It helps businesses assess their logistics performance, customer satisfaction, and operational bottlenecks.\n",
    "\n",
    "**Key Metrics in Delivery Performance Analysis**\n",
    "1. Delivery Status Breakdown – Number of orders in different stages (Delivered, In Transit, Pending).\n",
    "2. On-Time Delivery Rate – Measures whether orders were delivered by the expected date.\n",
    "3. Late Deliveries – Orders that missed the expected delivery date.\n",
    "4. Average Delivery Time – Time taken from order processing to delivery.\n",
    "5. Carrier Performance – Effectiveness of shipping companies in meeting deadlines."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e4522be0-79cc-44a6-b09d-229d0a3e3031",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['order_id', 'delivery_status', 'delivery_tracking_id',\n",
       "       'delivery_shipping_company', 'delivery_expected_delivery_date'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_deliveries.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6def982f-41d1-437b-a53a-9855579f5ce3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>32E8BD2B-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD4</td>\n",
       "      <td>Processing</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2025-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD5</td>\n",
       "      <td>Processing</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2025-03-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id delivery_status delivery_tracking_id delivery_shipping_company  \\\n",
       "0     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "1     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "2     ORD3         Shipped           32E8BD2B-E                  BlueDart   \n",
       "3     ORD4      Processing                 None                      None   \n",
       "4     ORD5      Processing                 None                      None   \n",
       "\n",
       "  delivery_expected_delivery_date  \n",
       "0                      2025-03-15  \n",
       "1                      2025-03-22  \n",
       "2                      2025-03-13  \n",
       "3                      2025-03-15  \n",
       "4                      2025-03-18  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert timestamp \n",
    "df_deliveries[\"delivery_expected_delivery_date\"] = pd.to_datetime(df_deliveries[\"delivery_expected_delivery_date\"])\n",
    "\n",
    "df_deliveries.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdc5da35-fe74-412f-9252-a9992fbd3b73",
   "metadata": {},
   "source": [
    "### **Handling Missing Values**\n",
    "\n",
    "If tracking_id or shipping_company is missing, they are replaced with \"PENDING\" and \"UNKNOWN\", respectively."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1a53d91e-1018-4811-ae25-82e8094fb41e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>32E8BD2B-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD4</td>\n",
       "      <td>Processing</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>2025-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD5</td>\n",
       "      <td>Processing</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>2025-03-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id delivery_status delivery_tracking_id delivery_shipping_company  \\\n",
       "0     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "1     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "2     ORD3         Shipped           32E8BD2B-E                  BlueDart   \n",
       "3     ORD4      Processing              PENDING                   UNKNOWN   \n",
       "4     ORD5      Processing              PENDING                   UNKNOWN   \n",
       "\n",
       "  delivery_expected_delivery_date  \n",
       "0                      2025-03-15  \n",
       "1                      2025-03-22  \n",
       "2                      2025-03-13  \n",
       "3                      2025-03-15  \n",
       "4                      2025-03-18  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fill missing values\n",
    "df_deliveries.fillna({\"delivery_tracking_id\": \"PENDING\", \n",
    "                    \"delivery_shipping_company\": \"UNKNOWN\"}, inplace=True)\n",
    "\n",
    "df_deliveries.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4386bc32-b54d-4ea1-afa5-bdd3c771e834",
   "metadata": {},
   "source": [
    "### **Calculating Delivery Status Breakdown**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c576eedf-3352-4a05-b538-a8993f7d6f40",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Status   Count\n",
      "0   Delivered  400190\n",
      "1     Shipped  200080\n",
      "2     Pending  199445\n",
      "3  Processing  100254\n",
      "4   Cancelled   50047\n",
      "5  In Transit   49984\n"
     ]
    }
   ],
   "source": [
    "# Count the number of orders in each status category\n",
    "delivery_status_counts = df_deliveries[\"delivery_status\"].value_counts()\n",
    "\n",
    "# Convert the counts into a DataFrame for better readability\n",
    "df_status_breakdown = pd.DataFrame({\"Status\": delivery_status_counts.index, \n",
    "                                    \"Count\": delivery_status_counts.values})\n",
    "\n",
    "# Output Result\n",
    "print(df_status_breakdown)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47d02e47-f1c8-42a0-b4f5-b164d2e94742",
   "metadata": {},
   "source": [
    "### **Calculating On-Time and Delayed Delivery Rate**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "63c8c355-a9e5-46a7-9150-3e49799c153d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD3</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>32E8BD2B-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD4</td>\n",
       "      <td>Processing</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>2025-03-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD5</td>\n",
       "      <td>Processing</td>\n",
       "      <td>PENDING</td>\n",
       "      <td>UNKNOWN</td>\n",
       "      <td>2025-03-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id delivery_status delivery_tracking_id delivery_shipping_company  \\\n",
       "0     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "1     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "2     ORD3         Shipped           32E8BD2B-E                  BlueDart   \n",
       "3     ORD4      Processing              PENDING                   UNKNOWN   \n",
       "4     ORD5      Processing              PENDING                   UNKNOWN   \n",
       "\n",
       "  delivery_expected_delivery_date  \n",
       "0                      2025-03-15  \n",
       "1                      2025-03-22  \n",
       "2                      2025-03-13  \n",
       "3                      2025-03-15  \n",
       "4                      2025-03-18  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Expected Delivery Dates\n",
    "df_deliveries.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "43828a6d-57d7-44a6-ad21-3b19a81a3f5e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>status</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>order_id</th>\n",
       "      <th>step</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-13 12:13:45</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-15 12:13:45</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-17 12:13:45</td>\n",
       "      <td>ORD1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-17 16:12:45</td>\n",
       "      <td>ORD2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-20 16:12:45</td>\n",
       "      <td>ORD2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       status           timestamp order_id  step\n",
       "0  Processing 2025-03-13 12:13:45     ORD1     1\n",
       "1     Shipped 2025-03-15 12:13:45     ORD1     2\n",
       "2   Delivered 2025-03-17 12:13:45     ORD1     3\n",
       "3  Processing 2025-03-17 16:12:45     ORD2     1\n",
       "4     Shipped 2025-03-20 16:12:45     ORD2     2"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Actual Delivery Dates\n",
    "df_order_history.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "6b7a199d-e9ac-4af9-9038-1fbec1199a11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Actual Delivery Date:\n",
      "           order_id actual_delivery_date\n",
      "0             ORD1  2025-03-17 12:13:45\n",
      "1           ORD100  2025-03-19 16:27:45\n",
      "2         ORD10000  2025-03-14 18:31:47\n",
      "3       ORD1000000  2025-03-14 18:35:11\n",
      "4        ORD100004  2025-03-19 20:34:05\n",
      "...            ...                  ...\n",
      "400185   ORD999989  2025-03-13 12:09:11\n",
      "400186    ORD99999  2025-03-14 22:20:05\n",
      "400187   ORD999991  2025-03-14 10:37:11\n",
      "400188   ORD999992  2025-03-17 14:05:11\n",
      "400189   ORD999998  2025-03-14 20:25:11\n",
      "\n",
      "[400190 rows x 2 columns]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Extract actual delivery dates\n",
    "df_actual_delivery = df_order_history[df_order_history[\"status\"] == \"Delivered\"].copy()\n",
    "df_actual_delivery = df_actual_delivery.groupby(\"order_id\")[\"timestamp\"].max().reset_index()\n",
    "df_actual_delivery.rename(columns={\"timestamp\": \"actual_delivery_date\"}, inplace=True)\n",
    "\n",
    "print(\"Actual Delivery Date:\\n\", df_actual_delivery)\n",
    "print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3cfb94f4-1d3f-493e-b0f9-5720bf63a38d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>actual_delivery_date</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "      <th>on_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>2025-03-17 12:13:45</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>2025-03-19 16:27:45</td>\n",
       "      <td>2025-03-20</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>2025-03-14 18:31:47</td>\n",
       "      <td>2025-03-13</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000000</td>\n",
       "      <td>2025-03-14 18:35:11</td>\n",
       "      <td>2025-03-17</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD100004</td>\n",
       "      <td>2025-03-19 20:34:05</td>\n",
       "      <td>2025-03-23</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     order_id actual_delivery_date delivery_expected_delivery_date  on_time\n",
       "0        ORD1  2025-03-17 12:13:45                      2025-03-15    False\n",
       "1      ORD100  2025-03-19 16:27:45                      2025-03-20     True\n",
       "2    ORD10000  2025-03-14 18:31:47                      2025-03-13    False\n",
       "3  ORD1000000  2025-03-14 18:35:11                      2025-03-17     True\n",
       "4   ORD100004  2025-03-19 20:34:05                      2025-03-23     True"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge with expected delivery dates\n",
    "df_merged = df_actual_delivery.merge(df_deliveries[[\"order_id\", \"delivery_expected_delivery_date\"]], on=\"order_id\", how=\"inner\")\n",
    "\n",
    "# Calculate On-Time Deliveries\n",
    "df_merged[\"on_time\"] = df_merged[\"actual_delivery_date\"] <= df_merged[\"delivery_expected_delivery_date\"]\n",
    "\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "210102c8-a001-465f-a37f-7971850c071a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "On-Time Delivery Rate: 50.10%\n",
      "Delayed Deliveries Rate: 49.90%\n"
     ]
    }
   ],
   "source": [
    "# Compute On-Time Delivery Rate\n",
    "on_time_rate = df_merged[\"on_time\"].mean() * 100  # Convert to percentage\n",
    "delayed_delivery_rate = 100 - on_time_rate\n",
    "\n",
    "# Output Results\n",
    "print(f\"On-Time Delivery Rate: {on_time_rate:.2f}%\")\n",
    "print(f\"Delayed Deliveries Rate: {delayed_delivery_rate:.2f}%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e5dea20-83eb-4438-9c40-337f9c193080",
   "metadata": {},
   "source": [
    "### **Calculating Average Delivery Time**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1b01cf17-e6f5-457b-8062-96c664c2e515",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_processing_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>2025-03-13 12:13:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD10</td>\n",
       "      <td>2025-03-09 14:35:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>2025-03-17 16:27:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000</td>\n",
       "      <td>2025-03-12 20:29:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>2025-03-09 18:31:47</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id order_processing_date\n",
       "0      ORD1   2025-03-13 12:13:45\n",
       "1     ORD10   2025-03-09 14:35:45\n",
       "2    ORD100   2025-03-17 16:27:45\n",
       "3   ORD1000   2025-03-12 20:29:45\n",
       "4  ORD10000   2025-03-09 18:31:47"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get Order Processing Time (earliest Processing timestamp per order)\n",
    "df_processing = df_order_history[df_order_history[\"status\"] == \"Processing\"].copy()\n",
    "df_processing = df_processing.groupby(\"order_id\")[\"timestamp\"].min().reset_index()\n",
    "df_processing.rename(columns={\"timestamp\": \"order_processing_date\"}, inplace=True)\n",
    "\n",
    "df_processing.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1a9d3d2b-d2a4-44bb-bffb-5db2c1403812",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>actual_delivery_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>2025-03-17 12:13:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>2025-03-19 16:27:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>2025-03-14 18:31:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000000</td>\n",
       "      <td>2025-03-14 18:35:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD100004</td>\n",
       "      <td>2025-03-19 20:34:05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     order_id actual_delivery_date\n",
       "0        ORD1  2025-03-17 12:13:45\n",
       "1      ORD100  2025-03-19 16:27:45\n",
       "2    ORD10000  2025-03-14 18:31:47\n",
       "3  ORD1000000  2025-03-14 18:35:11\n",
       "4   ORD100004  2025-03-19 20:34:05"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get Actual Delivery Time (latest Delivered timestamp per order)\n",
    "df_delivered = df_order_history[df_order_history[\"status\"] == \"Delivered\"].copy()\n",
    "df_delivered = df_delivered.groupby(\"order_id\")[\"timestamp\"].max().reset_index()\n",
    "df_delivered.rename(columns={\"timestamp\": \"actual_delivery_date\"}, inplace=True)\n",
    "\n",
    "df_delivered.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ed814e52-7e69-411f-8692-3535e9b0a164",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_processing_date</th>\n",
       "      <th>actual_delivery_date</th>\n",
       "      <th>delivery_time_days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>2025-03-13 12:13:45</td>\n",
       "      <td>2025-03-17 12:13:45</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD100</td>\n",
       "      <td>2025-03-17 16:27:45</td>\n",
       "      <td>2025-03-19 16:27:45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD10000</td>\n",
       "      <td>2025-03-09 18:31:47</td>\n",
       "      <td>2025-03-14 18:31:47</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD1000000</td>\n",
       "      <td>2025-03-12 18:35:11</td>\n",
       "      <td>2025-03-14 18:35:11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD100004</td>\n",
       "      <td>2025-03-16 20:34:05</td>\n",
       "      <td>2025-03-19 20:34:05</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     order_id order_processing_date actual_delivery_date  delivery_time_days\n",
       "0        ORD1   2025-03-13 12:13:45  2025-03-17 12:13:45                   4\n",
       "1      ORD100   2025-03-17 16:27:45  2025-03-19 16:27:45                   2\n",
       "2    ORD10000   2025-03-09 18:31:47  2025-03-14 18:31:47                   5\n",
       "3  ORD1000000   2025-03-12 18:35:11  2025-03-14 18:35:11                   2\n",
       "4   ORD100004   2025-03-16 20:34:05  2025-03-19 20:34:05                   3"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge both DataFrames on order_id\n",
    "df_merged = df_processing.merge(df_delivered, on=\"order_id\")\n",
    "\n",
    "# Calculate Delivery Time (in days)\n",
    "df_merged[\"delivery_time_days\"] = (df_merged[\"actual_delivery_date\"] - df_merged[\"order_processing_date\"]).dt.days\n",
    "\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "94a1df04-9aef-4b92-9a35-f83168e59431",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average Delivery Time: 4.00 days\n"
     ]
    }
   ],
   "source": [
    "# Compute Average Delivery Time\n",
    "average_delivery_time = df_merged[\"delivery_time_days\"].mean()\n",
    "\n",
    "# Output Result\n",
    "print(f\"Average Delivery Time: {average_delivery_time:.2f} days\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae06a638-c522-41c4-9bb3-f51130ef8456",
   "metadata": {},
   "source": [
    "## **Calculating Carrier Performance**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "0d426ada-3be9-4ac1-944e-d7b4d6bdb34c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "      <th>status</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>step</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-13 12:13:45</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-15 12:13:45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-17 12:13:45</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-17 16:12:45</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-20 16:12:45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id delivery_status delivery_tracking_id delivery_shipping_company  \\\n",
       "0     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "1     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "2     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "3     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "4     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "\n",
       "  delivery_expected_delivery_date      status           timestamp  step  \n",
       "0                      2025-03-15  Processing 2025-03-13 12:13:45     1  \n",
       "1                      2025-03-15     Shipped 2025-03-15 12:13:45     2  \n",
       "2                      2025-03-15   Delivered 2025-03-17 12:13:45     3  \n",
       "3                      2025-03-22  Processing 2025-03-17 16:12:45     1  \n",
       "4                      2025-03-22     Shipped 2025-03-20 16:12:45     2  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge both dataframes to get actual delivery date\n",
    "df_merged = df_deliveries.merge(df_order_history, on=\"order_id\", how=\"left\")\n",
    "\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "0309082a-850c-46b7-aa68-fff58d7c2ea0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>delivery_status</th>\n",
       "      <th>delivery_tracking_id</th>\n",
       "      <th>delivery_shipping_company</th>\n",
       "      <th>delivery_expected_delivery_date</th>\n",
       "      <th>status</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>step</th>\n",
       "      <th>on_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-13 12:13:45</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-15 12:13:45</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ORD1</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>313D0B0E-F</td>\n",
       "      <td>DHL</td>\n",
       "      <td>2025-03-15</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>2025-03-17 12:13:45</td>\n",
       "      <td>3</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "      <td>Processing</td>\n",
       "      <td>2025-03-17 16:12:45</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ORD2</td>\n",
       "      <td>Delivered</td>\n",
       "      <td>6792BDB5-E</td>\n",
       "      <td>BlueDart</td>\n",
       "      <td>2025-03-22</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>2025-03-20 16:12:45</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  order_id delivery_status delivery_tracking_id delivery_shipping_company  \\\n",
       "0     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "1     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "2     ORD1       Delivered           313D0B0E-F                       DHL   \n",
       "3     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "4     ORD2       Delivered           6792BDB5-E                  BlueDart   \n",
       "\n",
       "  delivery_expected_delivery_date      status           timestamp  step  \\\n",
       "0                      2025-03-15  Processing 2025-03-13 12:13:45     1   \n",
       "1                      2025-03-15     Shipped 2025-03-15 12:13:45     2   \n",
       "2                      2025-03-15   Delivered 2025-03-17 12:13:45     3   \n",
       "3                      2025-03-22  Processing 2025-03-17 16:12:45     1   \n",
       "4                      2025-03-22     Shipped 2025-03-20 16:12:45     2   \n",
       "\n",
       "   on_time  \n",
       "0     True  \n",
       "1    False  \n",
       "2    False  \n",
       "3     True  \n",
       "4     True  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate on-time delivery (Actual Date <= Expected Date)\n",
    "df_merged[\"on_time\"] = df_merged[\"timestamp\"] <= df_merged[\"delivery_expected_delivery_date\"]\n",
    "\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e1858a1c-c187-4f07-bfa3-72046e94abe4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  delivery_shipping_company  on_time_rate\n",
      "0                  BlueDart     81.816928\n",
      "1                       DHL     82.073783\n",
      "2                     FedEx     81.832443\n",
      "3                   UNKNOWN    100.000000\n",
      "4                       UPS     81.832748\n",
      "5                      USPS     81.906864\n"
     ]
    }
   ],
   "source": [
    "# Calculate carrier performance\n",
    "carrier_performance = df_merged.groupby(\"delivery_shipping_company\")[\"on_time\"].agg([\"sum\", \"count\"])\n",
    "carrier_performance[\"on_time_rate\"] = (carrier_performance[\"sum\"] / carrier_performance[\"count\"]) * 100\n",
    "\n",
    "# Format results\n",
    "carrier_performance = carrier_performance[[\"on_time_rate\"]].reset_index()\n",
    "\n",
    "# Output Result\n",
    "print(carrier_performance)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d96840cf-6e89-47b0-bb42-50ad2375e717",
   "metadata": {},
   "source": [
    "## **Additional Analysis**\n",
    "\n",
    "### **Sales & Revenue Analysis**\n",
    "**Objective:** Identify top-performing products & revenue trends  \n",
    "**Why?** Helps in stock management, promotions, and pricing strategies  \n",
    "\n",
    "**Analysis to Perform:**\n",
    "- Find the best-selling products by quantity & revenue\n",
    "- Identify underperforming products (low sales, high returns)\n",
    "- Track monthly/yearly revenue trends\n",
    "- Measure impact of discounts on sales\n",
    "- Analyze seasonal trends in purchases\n",
    "\n",
    "### **Payment Method Preferences**\n",
    "**Objective:** Understand which payment methods are most used  \n",
    "**Why?** Helps in improving checkout experience and offering better payment options  \n",
    "\n",
    "**Analysis to Perform:**\n",
    "- Find most used payment methods\n",
    "- Compare discount amounts given per payment method\n",
    "- Identify high-risk payment methods (refunds, failed transactions)\n",
    "\n",
    "### **Order Processing & Delivery Efficiency**\n",
    "**Objective:** Measure how efficient the delivery process is  \n",
    "**Why?** Helps in improving delivery times, reducing delays, and optimizing logistics  \n",
    "\n",
    "**Analysis to Perform:**\n",
    "- Find average time taken for orders to be delivered\n",
    "- Identify most delayed orders & reasons for delays\n",
    "- Compare delivery performance across shipping companies\n",
    "- Track cancelled or returned orders\n",
    "\n",
    "### **Order History & Status Tracking**\n",
    "**Objective:** Track the journey of an order from placement to delivery  \n",
    "**Why?** Helps in improving customer service, resolving complaints, and optimizing order fulfillment\n",
    "\n",
    "**Analysis to Perform:**\n",
    "- Track percentage of orders in each stage (Processing, Shipped, Delivered, Cancelled)\n",
    "- Analyze how long each stage takes on average\n",
    "- Find patterns in delayed or cancelled orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7dd40af2-2736-4550-9a8d-a25580d4223a",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
